con <- dbConnect(
  RMariaDB::MariaDB(),
  host = "relational.fit.cvut.cz",
  port = 3306,
  username = "guest",
  password = "relational",
  dbname = "sakila"
)
dbListTables(con)
##  [1] "actor"         "address"       "category"      "city"         
##  [5] "country"       "customer"      "film"          "film_actor"   
##  [9] "film_category" "film_text"     "inventory"     "language"     
## [13] "payment"       "rental"        "staff"         "store"

Q1

SELECT
    customer.first_name,
    customer.last_name,
    customer.email,
    COUNT(rental.rental_id) AS num_film_rentals
FROM
    customer
JOIN rental ON customer.customer_id = rental.customer_id
GROUP BY
    customer.customer_id, customer.first_name, customer.last_name, customer.email
ORDER BY
    num_film_rentals DESC
LIMIT 10;
Displaying records 1 - 10
first_name last_name email num_film_rentals
ELEANOR HUNT 46
KARL SEAL 45
CLARA SHAW 42
MARCIA DEAN 42
TAMMY SANDERS 41
SUE PETERS 40
WESLEY BULL 40
TIM CARY 39
RHONDA KENNEDY 39
MARION SNYDER 39

Q2

SELECT
    film.title,
    film.description
FROM
    film
LEFT JOIN inventory ON film.film_id = inventory.film_id
LEFT JOIN rental ON inventory.inventory_id = rental.inventory_id
WHERE
    rental.rental_id IS NULL
ORDER BY
    film.title;
Displaying records 1 - 10
title description
ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
ALICE FANTASIA A Emotional Drama of a A Shark And a Database Administrator who must Vanquish a Pioneer in Soviet Georgia
APOLLO TEEN A Action-Packed Reflection of a Crocodile And a Explorer who must Find a Sumo Wrestler in An Abandoned Mine Shaft
ARGONAUTS TOWN A Emotional Epistle of a Forensic Psychologist And a Butler who must Challenge a Waitress in An Abandoned Mine Shaft
ARK RIDGEMONT A Beautiful Yarn of a Pioneer And a Monkey who must Pursue a Explorer in The Sahara Desert
ARSENIC INDEPENDENCE A Fanciful Documentary of a Mad Cow And a Womanizer who must Find a Dentist in Berlin
BOONDOCK BALLROOM A Fateful Panorama of a Crocodile And a Boy who must Defeat a Monkey in The Gulf of Mexico
BUTCH PANTHER A Lacklusture Yarn of a Feminist And a Database Administrator who must Face a Hunter in New Orleans
CATCH AMISTAD A Boring Reflection of a Lumberjack And a Feminist who must Discover a Woman in Nigeria
CHINATOWN GLADIATOR A Brilliant Panorama of a Technical Writer And a Lumberjack who must Escape a Butler in Ancient India

Q3

SELECT
    category.name AS category,
    AVG(film.length) AS avg_length
FROM
    category
JOIN film_category ON category.category_id = film_category.category_id
JOIN film ON film_category.film_id = film.film_id
GROUP BY
    category.category_id, category.name
ORDER BY
    avg_length DESC;
Displaying records 1 - 10
category avg_length
Sports 128.2027
Games 127.8361
Foreign 121.6986
Drama 120.8387
Comedy 115.8276
Family 114.7826
Music 113.6471
Travel 113.3158
Horror 112.4821
Classics 111.6667

Q4

SELECT
    actor.actor_id,
    actor.first_name,
    actor.last_name,
    COUNT(film_actor.film_id) AS film_count
FROM
    actor
JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY
    actor.actor_id, actor.first_name, actor.last_name
ORDER BY
    film_count DESC
LIMIT 5;
5 records
actor_id first_name last_name film_count
107 GINA DEGENERES 42
102 WALTER TORN 41
198 MARY KEITEL 40
181 MATTHEW CARREY 39
23 SANDRA KILMER 37

Q5

SELECT DISTINCT
    customer.first_name,
    customer.last_name
FROM
    customer
JOIN rental ON customer.customer_id = rental.customer_id
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film_actor ON inventory.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
WHERE
    actor.first_name = 'Johnny' AND actor.last_name = 'Depp';
0 records
first_name last_name

Q6

SELECT
    film.title,
    SUM(payment.amount) AS total_revenue
FROM
    film
JOIN inventory ON film.film_id = inventory.film_id
JOIN rental ON inventory.inventory_id = rental.inventory_id
JOIN payment ON rental.rental_id = payment.rental_id
GROUP BY
    film.title
ORDER BY
    total_revenue DESC
LIMIT 10;
Displaying records 1 - 10
title total_revenue
TELEGRAPH VOYAGE 231.73
WIFE TURN 223.69
ZORRO ARK 214.69
GOODFELLAS SALUTE 209.69
SATURDAY LAMBS 204.72
TITANS JERK 201.71
TORQUE BOUND 198.72
HARRY IDAHO 195.70
INNOCENT USUAL 191.74
HUSTLER PARTY 190.78 
dbDisconnect(con)

PLOTLY

Q1

options(repos = c(CRAN = "https://cran.rstudio.com"))
install.packages("plotly")
## 
## The downloaded binary packages are in
##  /var/folders/8_/x_11_k6n63x968_lr6dggkt80000gn/T//RtmpqTiAdv/downloaded_packages
library(plotly)
## Loading required package: ggplot2
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
plot <- plot_ly(data = diamonds, x = ~carat, y = ~price, mode = "markers", trace = "scatter")

plot <- plot %>% layout(
  title = "Scatterplot of Carat vs. Price",
  xaxis = list(title = "Carat"),
  yaxis = list(title = "Price")
)
plot
## No trace type specified:
##   Based on info supplied, a 'scatter' trace seems appropriate.
##   Read more about this trace type -> https://plotly.com/r/reference/#scatter
## Warning: 'scatter' objects don't have these attributes: 'trace'
## Valid attributes include:
## 'cliponaxis', 'connectgaps', 'customdata', 'customdatasrc', 'dx', 'dy', 'error_x', 'error_y', 'fill', 'fillcolor', 'fillpattern', 'groupnorm', 'hoverinfo', 'hoverinfosrc', 'hoverlabel', 'hoveron', 'hovertemplate', 'hovertemplatesrc', 'hovertext', 'hovertextsrc', 'ids', 'idssrc', 'legendgroup', 'legendgrouptitle', 'legendrank', 'line', 'marker', 'meta', 'metasrc', 'mode', 'name', 'opacity', 'orientation', 'selected', 'selectedpoints', 'showlegend', 'stackgaps', 'stackgroup', 'stream', 'text', 'textfont', 'textposition', 'textpositionsrc', 'textsrc', 'texttemplate', 'texttemplatesrc', 'transforms', 'type', 'uid', 'uirevision', 'unselected', 'visible', 'x', 'x0', 'xaxis', 'xcalendar', 'xhoverformat', 'xperiod', 'xperiod0', 'xperiodalignment', 'xsrc', 'y', 'y0', 'yaxis', 'ycalendar', 'yhoverformat', 'yperiod', 'yperiod0', 'yperiodalignment', 'ysrc', 'key', 'set', 'frame', 'transforms', '_isNestedKey', '_isSimpleKey', '_isGraticule', '_bbox'

Q2

ggplot is great for static graphs that do not need to be interacted with. it is simple, intuitive and widely used.

plotly is designed for dynamic data visualizations that allow for user interaction. it can also create more advanced graphs like 3D plots, animations, or maps.

you’d definitely want to use gglot2 for reports/papers that will be printed out and cannot be accessed online for interactive use.

#Q3

library(plotly)
library(gapminder)
data(gapminder)
# Install and load necessary libraries
install.packages("plotly")
## 
## The downloaded binary packages are in
##  /var/folders/8_/x_11_k6n63x968_lr6dggkt80000gn/T//RtmpqTiAdv/downloaded_packages
library(plotly)

# Load the gapminder dataset
data(gapminder)

# Select data for the United States
pal <- subset(gapminder, country == "Israel")

q3 <- plot_ly(
  data = pal,
  x = ~year,
  y = ~lifeExp,
  type = "scatter",
  mode = "lines+markers",
  name = "Life Expectancy",
  yaxis = "y",
  hoverinfo = "y+name"
) %>%
  add_trace(
    y = ~gdpPercap,
    name = "GDP per Capita",
    yaxis = "y2",
    hoverinfo = "y+name"
  ) %>%
  layout(
    title = "Life Expectancy and GDP per Capita (Israel)",
    xaxis = list(title = "Year"),
    yaxis = list(title = "Life Expectancy"),
    yaxis2 = list(
      title = "GDP per Capita",
      overlaying = "y",
      side = "right"
    )
  )
q3

this chart shows how life expectancy in years and GDP per Capita have changed between 1952 and 2007. we observe the trend that both variables have increased simultaneously suggesting a positive correlation between the two variables. the narrative advanced by this comparison is that economic growth might be associated with better healthcare systems that allow people to live longer lives. It is important to note that we do not have evidence to say that the relationship is causal.